﻿using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Data.SqlClient;
using StudentManagement.Entities;

namespace StudentManagement.DAL
{
    public class TbcUserDAL
    {
        private readonly string _connectionString;

        public TbcUserDAL(string connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// 根据ID获取用户
        /// </summary>
        public TbcUserInfo GetById(int userId)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                string sql = "SELECT * FROM TbcUserInfo WHERE TbcUserID = @UserId";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@UserId", userId);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return MapToEntity(reader);
                        }
                        return null;
                    }
                }
            }
        }

        /// <summary>
        /// 添加用户
        /// </summary>
        public int Add(TbcUserInfo user)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                string sql = @"INSERT INTO TbcUserInfo (TbcUserName, TbcPassword, TbcEmail, TbcPhone, TbcAddress) 
                              VALUES (@UserName, @Password, @Email, @Phone, @Address);
                              SELECT SCOPE_IDENTITY();";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@UserName", user.TbcUserName ?? "");
                    cmd.Parameters.AddWithValue("@Password", user.TbcPassword ?? "");
                    cmd.Parameters.AddWithValue("@Email", (object)user.TbcEmail ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@Phone", (object)user.TbcPhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@Address", (object)user.TbcAddress ?? DBNull.Value);

                    return Convert.ToInt32(cmd.ExecuteScalar());
                }
            }
        }

        /// <summary>
        /// 更新用户
        /// </summary>
        public bool Update(TbcUserInfo user)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                string sql = @"UPDATE TbcUserInfo SET 
                              TbcUserName = @UserName,
                              TbcPassword = @Password,
                              TbcEmail = @Email,
                              TbcPhone = @Phone,
                              TbcAddress = @Address
                              WHERE TbcUserID = @UserId";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@UserName", user.TbcUserName ?? "");
                    cmd.Parameters.AddWithValue("@Password", user.TbcPassword ?? "");
                    cmd.Parameters.AddWithValue("@Email", (object)user.TbcEmail ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@Phone", (object)user.TbcPhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@Address", (object)user.TbcAddress ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@UserId", user.TbcUserID);

                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        public bool Delete(int userId)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                string sql = "DELETE FROM TbcUserInfo WHERE TbcUserID = @UserId";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@UserId", userId);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        public (List<TbcUserInfo> Data, int TotalCount) GetPagedList(string keyword, int pageIndex, int pageSize)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                // 获取总数
                string countSql = "SELECT COUNT(*) FROM TbcUserInfo WHERE 1=1";
                if (!string.IsNullOrEmpty(keyword))
                {
                    countSql += " AND (TbcUserName LIKE @Keyword OR TbcEmail LIKE @Keyword)";
                }

                using (SqlCommand countCmd = new SqlCommand(countSql, conn))
                {
                    if (!string.IsNullOrEmpty(keyword))
                    {
                        countCmd.Parameters.AddWithValue("@Keyword", $"%{keyword}%");
                    }
                    int totalCount = Convert.ToInt32(countCmd.ExecuteScalar());

                    // 获取分页数据
                    string dataSql = @"
                        SELECT * FROM (
                            SELECT *, ROW_NUMBER() OVER (ORDER BY TbcUserID) as RowNum 
                            FROM TbcUserInfo 
                            WHERE 1=1";

                    if (!string.IsNullOrEmpty(keyword))
                    {
                        dataSql += " AND (TbcUserName LIKE @Keyword OR TbcEmail LIKE @Keyword)";
                    }

                    dataSql += @") as T 
                        WHERE RowNum BETWEEN @StartIndex AND @EndIndex";

                    List<TbcUserInfo> data = new List<TbcUserInfo>();
                    using (SqlCommand dataCmd = new SqlCommand(dataSql, conn))
                    {
                        if (!string.IsNullOrEmpty(keyword))
                        {
                            dataCmd.Parameters.AddWithValue("@Keyword", $"%{keyword}%");
                        }
                        dataCmd.Parameters.AddWithValue("@StartIndex", (pageIndex - 1) * pageSize + 1);
                        dataCmd.Parameters.AddWithValue("@EndIndex", pageIndex * pageSize);

                        using (SqlDataReader reader = dataCmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                data.Add(MapToEntity(reader));
                            }
                        }
                    }

                    return (data, totalCount);
                }
            }
        }

        private TbcUserInfo MapToEntity(SqlDataReader reader)
        {
            return new TbcUserInfo
            {
                TbcUserID = Convert.ToInt32(reader["TbcUserID"]),
                TbcUserName = reader["TbcUserName"].ToString(),
                TbcPassword = reader["TbcPassword"].ToString(),
                TbcEmail = reader["TbcEmail"] == DBNull.Value ? null : reader["TbcEmail"].ToString(),
                TbcPhone = reader["TbcPhone"] == DBNull.Value ? null : reader["TbcPhone"].ToString(),
                TbcAddress = reader["TbcAddress"] == DBNull.Value ? null : reader["TbcAddress"].ToString(),
                TbcRegDateTime = reader["TbcRegDateTime"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(reader["TbcRegDateTime"])
            };
        }
    }
}